package com.zhiqingchun.dao.article.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.zhiqingchun.common.Page;
import com.zhiqingchun.dao.BaseDao;
import com.zhiqingchun.dao.article.ArticleResult;
import com.zhiqingchun.dao.article.IArticleDao;
import com.zhiqingchun.pojo.ArticleDO;

public class ArticleDao extends BaseDao implements IArticleDao {
	
	private DataSource dataSource;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}


	@Override
	public ArticleDO byId(int no) {
		String sql = "SELECT a.*, u.usr, main.name as main_name, main.id as main_id, "
				+ "sub.name as sub_name, sub.id as sub_id "
				+ "FROM article a, usr u, category main, category sub "
				+ "where a.id = ? and a.author = u.id and sub.id = a.category and main.id = sub.parent";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, no);
			
			ArticleDO articleDO = new ArticleDO();
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				articleDO.setId(no);
				articleDO.setParentCategoryName(rs.getString("MAIN_NAME"));
				articleDO.setParentCategory(rs.getInt("MAIN_ID"));
				articleDO.setCategoryName(rs.getString("SUB_NAME"));
				articleDO.setCategory(rs.getInt("SUB_ID"));
				articleDO.setTitle(rs.getString("TITLE"));
				articleDO.setContent(rs.getString("CONTENT"));
				articleDO.setAuthor(rs.getInt("AUTHOR"));
				articleDO.setAuthorName(rs.getString("USR"));
				articleDO.setInsertTime(rs.getTimestamp("INSERT_TIME"));
				articleDO.setCollection(rs.getInt("Collection"));
			}
			rs.close();
			ps.close();
			return articleDO;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					
				}
			}
		}
	}

	@Override
	public List<ArticleDO> list(ArticleDO articleDO, Page page) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT a.*, u.usr, main.name as main_name, main.id as main_id, ")
			.append("sub.name as sub_name, sub.id as sub_id ")
			.append("FROM article a, usr u, category main, category sub ")
			.append("where a.author = u.id and sub.id = a.category and main.id = sub.parent ");
		List<Object> param = new ArrayList<Object>();
		if (articleDO != null && articleDO.getCategory() != null && articleDO.getCategory() > 0) {
			sql.append(" and a.category = ?");
			param.add(articleDO.getCategory());
		}
		if (articleDO != null && articleDO.getAuthor() != null && articleDO.getAuthor() > 0) {
			sql.append(" and a.author = ?");
			param.add(articleDO.getAuthor());
		}
		sql.append(" order by a.insert_time desc");
		List<Map<String, Object>> returnList = getJdbcBaseDao().findPageList(sql.toString(), param.toArray(), page);
		List<ArticleDO> list = new ArrayList<ArticleDO>();
		for (Map<String, Object> map : returnList) {
			ArticleDO a = new ArticleDO();
			a.setId(Integer.parseInt(map.get("id").toString()));
			a.setTitle(map.get("title").toString());
			Integer _comment =  Integer.parseInt(map.get("comment").toString());
			Integer _collection = Integer.parseInt(map.get("collection").toString());
			a.setCollection(_comment);
			a.setComment(_collection);
			a.setTotal(_comment * 2 + _collection);
			a.setAuthorName(map.get("usr").toString());
			a.setInsertTime((Date) map.get("INSERT_TIME"));
			list.add(a);
		}
		return list;
	}

	@Override
	public ArticleResult collect(int id, int userId) {
		String sql = "select * from usr_article where usr_id = ? and article_id = ?";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			conn.setAutoCommit(false);
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, id);
			ArticleResult articleResult = new ArticleResult();
			ResultSet rs = ps.executeQuery();
			int i = 1; // 用于给article表，收藏时collection字段加1（默认），取消时减1
			if (rs.next()) { // 如果已经收藏了
				String sql2 = "delete from usr_article where usr_id = ? and article_id = ?";
				ps = conn.prepareStatement(sql2);
				ps.setInt(1, userId);
				ps.setInt(2, id);
				ps.executeUpdate();
				articleResult.setCollectStatus(2);
				i = -1;
			} else {
				String sql2 = "insert into usr_article (usr_id, article_id, time) values (?, ?, ?)";
				ps = conn.prepareStatement(sql2);
				ps.setInt(1, userId);
				ps.setInt(2, id);
				ps.setTimestamp(3, new Timestamp(new Date().getTime()));
				ps.executeUpdate();
				articleResult.setCollectStatus(1);
			}
			rs.close();
			ps.close();
			
			PreparedStatement ps2 = conn.prepareStatement("update article set collection "
					+ "= collection + ? where id = ?");
			ps2.setInt(1, i);
			ps2.setInt(2, id);
			ps2.executeUpdate();
			ps2.close();
			
			conn.commit();
			return articleResult;
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (Exception e2) {
				}
			}
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					
				}
			}
		}
		
	}

	@Override
	public boolean isCollect(int id, int userId) {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from usr_article where usr_id = ? and article_id = ?");
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, userId);
			ps.setInt(2, id);
			ResultSet rs = ps.executeQuery();
			boolean result = false;
			if (rs.next()) {
				result = true;
			} 
			rs.close();
			ps.close();
			return result;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					
				}
			}
		}
	}

	@Override
	public ArticleResult add(ArticleDO articleDO) {
		String sql = "insert into article (title, content, author, category, insert_time) "
				+ "values (?, ?, ?, ?, ?)";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, articleDO.getTitle());
			ps.setString(2, articleDO.getContent());
			ps.setInt(3, articleDO.getAuthor());
			ps.setInt(4, articleDO.getCategory());
			ps.setTimestamp(5, new Timestamp(articleDO.getInsertTime().getTime()));
			ps.executeUpdate();
			ps.close();
			
			ArticleResult articleResult = new ArticleResult();
			articleResult.setSuccess(true);
			return articleResult;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					
				}
			}
		}
	}

	@Override
	public ArticleResult modify(ArticleDO articleDO) {
		String sql = "update article set title=?, content=?, category=? where id=?";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, articleDO.getTitle());
			ps.setString(2, articleDO.getContent());
			ps.setInt(3, articleDO.getCategory());
			ps.setInt(4, articleDO.getId());
			ps.executeUpdate();
			ps.close();
			
			ArticleResult articleResult = new ArticleResult();
			articleResult.setSuccess(true);
			return articleResult;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					
				}
			}
		}
	}

	@Override
	public ArticleResult delete(ArticleDO articleDO) {
		String sql = "delete from article where id=?";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, articleDO.getId());
			ps.executeUpdate();
			ps.close();
			
			ArticleResult articleResult = new ArticleResult();
			articleResult.setSuccess(true);
			return articleResult;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					
				}
			}
		}
	}

}
